package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.VideoAndCollectDao;
import com.bilibili.dto.VideoAndCollect;

public class VideoAndCollectDaoImpl implements VideoAndCollectDao {

	@Override
	public List<VideoAndCollect> queryAllCollectById(int userId,int pageNum,int pageSize,String title) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT u1.nickname,v1.*,v2.collection_time FROM user u1 LEFT JOIN video v1 " + 
				"ON u1.user_id = v1.uploader_id " + 
				"LEFT JOIN video_collection v2 " + 
				"ON v1.video_id = v2.video_id " + 
				"WHERE v2.user_id = ? AND v1.status = 1 ";
		if(title != null && !"".equals(title)) {
			sql+="AND v1.title like concat('%',?,'%') ";
		}
		sql+="ORDER BY v2.collection_time DESC limit ?,?";
		List<VideoAndCollect> list = new ArrayList<VideoAndCollect>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			ps.setInt(1, userId);
			if(title != null && !"".equals(title)) {
				ps.setString(2, title);
				ps.setInt(3, (pageNum-1)*pageSize);
				ps.setInt(4, pageSize);
			}else {
				ps.setInt(2, (pageNum-1)*pageSize);
				ps.setInt(3, pageSize);
			}
			rs = ps.executeQuery();			
			while(rs.next()) {
				VideoAndCollect vac = new VideoAndCollect();
				vac.setVideoId(rs.getInt("video_id"));
				vac.setNickname(rs.getString("nickname"));
				vac.setTitle(rs.getString("title"));
			    vac.setDescription(rs.getString("description"));
			    vac.setCoverUrl(rs.getString("cover_url"));
			    vac.setVideoUrl(rs.getString("video_url"));
			    vac.setCategoryId(rs.getInt("category_id"));
			    vac.setCreateTime(rs.getDate("create_time"));
			    vac.setDuration(rs.getInt("duration"));
			    vac.setViewCount(rs.getInt("view_count"));
			    vac.setLikeCount(rs.getInt("like_count"));
			    vac.setCoinCount(rs.getInt("coin_count"));
			    vac.setCollectCount(rs.getInt("collect_count"));
			    vac.setCollection_time(rs.getDate("collection_time"));
				list.add(vac);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int queryAllCollectNum(int userId,String title) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT count(1) as total FROM user u1 LEFT JOIN video v1 " + 
				"ON u1.user_id = v1.uploader_id " + 
				"LEFT JOIN video_collection v2 " + 
				"ON v1.video_id = v2.video_id "+ 
				"WHERE v2.user_id = ? AND v1.status = 1 ";
		if(title != null && !"".equals(title)) {
			sql+="AND v1.title like concat('%',?,'%')";
		}
		int row = 0;
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			ps.setInt(1, userId);
			if(title != null && !"".equals(title)) {
				ps.setString(2, title);
			}
			rs = ps.executeQuery();			
			if(rs.next()) {
				row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}

}
